﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

namespace DLL
{
    public static class DB
    {
        private static string connection = ConfigurationManager.AppSettings.Get("LocalSqlServer");
        private static SqlConnection myConnection = new SqlConnection(connection);

        // Get all customers in arraylist
        public static ArrayList getAllCustomers()
        {
            SqlDataReader reader = null;
            ArrayList customersArray = new ArrayList();
            try
            {
                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }

                SqlCommand command = new SqlCommand("select * from customers;");
                command.Connection = myConnection;
                reader = command.ExecuteReader();

                while (reader.Read())
                {
                    // Customers customers = new Customers();
                    //customers.Id = Int32.Parse(reader["id"].ToString().Trim());
                    //customers.Cust_name = reader["cust_name"].ToString().Trim();
                    //customers.Entry_date = DateTime.Parse(reader["entry_date"].ToString().Trim());

                    //customersArray.Add(customers);
                }
            }
            catch (Exception air)
            {
                System.Console.WriteLine(air.ToString());
                myConnection.Close();
                return null;
            }
            myConnection.Close();
            return customersArray;
        }


        public List<reservations> getAllReservations()
        {
            string sqlComm = @"SELECT TOP 1000 [Reservation].[Id],
                            [Company].[Name],
                            [Reservation].[StartDate],
                            [Reservation].[EndDate],
                            [Reservation].[CompanyId],
                            [Location].[Name] as locationName,
                            [Reservation].[CreateDate]
                            FROM [Pj].[dbo].[Reservation]
                            INNER JOIN [Pj].[dbo].[Company] on Reservation.CompanyId = Company.Id
                            INNER JOIN [Pj].[dbo].[Location] on Reservation.LocationId = Location.Id";
            SqlCommand comm = new SqlCommand(sqlComm, myConnection);
            myConnection.Open();
            SqlDataReader reader = comm.ExecuteReader();
            reader.Read();
            List<reservations> reservaties = new List<reservations>();
            while (reader.Read())
            {
                reservations r = new reservations();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    r.Id = reader.GetInt32(0);
                    r.Name = reader.GetString(1);
                    r.StartDate = reader.GetDateTime(2);
                    r.EndDate = reader.GetDateTime(3);
                    r.CompanyId = reader.GetInt32(4);
                    r.locationName = reader.GetString(5);
                    r.CreateDate = reader.GetDateTime(6);
                }
                reservaties.Add(r);
            }
            return reservaties;
        }
    }
}
